Imports CAPA_Entidad
Imports System.Data
Imports System.Data.SqlClient
Imports CAPA_Utilitarios
Public Class BandejaDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    Function verificarExistenciaBandeja(ByVal codBan As String) As Boolean
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()
            Dim query As String = "SELECT * " & _
                                  "FROM Bandeja " & _
                                  "WHERE BAN_CIA+BAN_SUC+BAN_ALM+BAN_Codigo = '" & codBan & "' " & _
                                  "AND BAN_Activo <> '0'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count <> 0 Then
                valor = True
            End If
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function verificarEstadoBandeja(ByVal codigoBandeja As String) As String
        Dim texto As String = ""
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()
            Dim query As String = "SELECT BAE_Estado " & _
                                  "FROM Bandeja " & _
                                  "WHERE BAN_CIA+BAN_SUC+BAN_ALM+BAN_Codigo = '" & codigoBandeja & "' " & _
                                  "AND BAN_Activo = '1'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count <> 0 Then
                texto = ds.Tables(0).Rows(0)("BAE_Estado").ToString.Trim
            End If
        Catch ex As Exception
            texto = "-1"
        Finally
            con.Close()
        End Try

        Return texto
    End Function

    Function obtenerBandejaPersona(ByVal codCIA As String, ByVal codSUC As String, ByVal codPersona As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()
        Try
            con.Open()

            Dim query As String = "SELECT CIA+SUC_Codigo+'00'+BAN_Codigo AS CodBandeja, " & _
                                  "CONVERT(VARCHAR(15), USU_FecBandeja, 103) + ' ' + CONVERT(VARCHAR(15), USU_FecBandeja, 108) AS Fecha " & _
                                  "FROM Usuario " & _
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND USU_Codigo = @PER " & _
                                  "AND USU_Activo = '1'"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@PER", codPersona)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Sub cambiarEstadoBandeja(ByVal codigoBandeja As String, ByVal estado As String)
        Dim con As SqlConnection = conex.obtenerConexion()
        Dim valor As Boolean = False

        Try
            con.Open()

            Dim query As String = "UPDATE Bandeja " & _
                                  "SET BAE_Estado = @Estado " & _
                                  "WHERE BAN_CIA+BAN_SUC+BAN_ALM+BAN_CODIGO = @bandeja " & _
                                  "AND BAN_Activo = '1'"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@bandeja", codigoBandeja)
                .Parameters.AddWithValue("@Estado", estado)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try
    End Sub

    Function verificarBandejaItems(ByVal codigoBandeja As String, ByVal dcaCodigo As String) As Boolean
        Dim con As SqlConnection = conex.obtenerConexion()
        Dim valor As Boolean = False

        Try
            con.Open()

            Dim query As String = "SELECT COUNT(*) AS CONTAR " & _
                                  "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " & _
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " & _
                                  "ON dadpub.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpu.DADPU_Activo = '1' " & _
                                  "WHERE dadpu.CIA+dadpu.SUC_Codigo+dadpu.ALM_Codigo+dadpub.BAN_Codigo = '" & codigoBandeja & "' " & _
                                  "AND DCA_Codigo = '" & dcaCodigo & "' " & _
                                  "AND dadpub.DADPUB_Cantidad > 0 " & _
                                  "AND dadpub.DADPUB_Activo = '1'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If CInt(ds.Tables(0).Rows(0)("CONTAR").ToString.Trim) <> 0 Then
                valor = True
            End If
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function verificarBandejaWithItems(ByVal codCIA As String, ByVal codSUC As String, ByVal codTipo As String, ByVal codDCA As String, ByVal codDADPU As String, ByVal codBandeja As String) As Boolean
        Dim con As SqlConnection = conex.obtenerConexion()
        Dim valor As Boolean = False

        Try
            con.Open()

            Dim query As String = "SELECT COUNT(*) AS Contar " & _
                                  "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja " & _
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDO " & _
                                  "AND DCA_Codigo = @DCA AND DADPU_Codigo = @DADPU " & _
                                  "AND CIA + SUC_Codigo + ALM_Codigo + BAN_Codigo = @BAN " & _
                                  "AND DADPUB_Cantidad > 0 AND DADPUB_Activo = '1'"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDO", codTipo)
                .Parameters.AddWithValue("@DCA", codDCA)
                .Parameters.AddWithValue("@DADPU", codDADPU)
                .Parameters.AddWithValue("@BAN", codBandeja)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim) <> 0 Then
                valor = True
            End If
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

End Class
